Fine-Tuning JDBC Application Performance
This section provides some tips for fine-tuning the performance of your JDBC applications.
Reducing Download Time
Generally, the time that it takes for applets to download is determined by the following factors:
JDK 1.2-compatible and higher Java Virtual Machines support JAR files, which reduces the number of HTTP requests because all the class files are packaged together in the JAR file. The JAR format also allows you to compress the packaged files, which further optimizes the download.
To reduce download time by using JAR files:
For example:
<html> <applet width=100 height=100 code=MyApplet codebase=. archive=myapplet.jar> <param name=ConfigFile value=Config.txt> </applet>The SequeLink for JDBC driver is packaged into the following JAR files:
To use the JDBC driver from within your applet, specify these JAR files in the archive tag as shown:
<html> <applet width=100 height=100 code=MyApplet codebase=. archive=myapplet.jar,sljc.jar,slssl.jar> <param name=ConfigFile value=Config.txt> </applet>Fetching BigDecimal Objects
JDBC 1.22 defines getBigDecimal() with a scale parameter. When the JDBC driver fetches a BigDecimal object from a database, it rescales it using the scale specified by the application. This additional processing can downgrade system performance, particularly when large numbers of BigDecimal objects are fetched by your application.
To eliminate this additional rescaling, JDBC 2.0 defines an overloaded version of getBigDecimal, without the scale parameter. This method allows the JDBC driver to return the BigDecimal object with the original precision.
Using Database Metadata Methods
Because database metadata methods that generate Resultset objects are slow compared to other JDBC methods, their frequent use can impair system performance. The guidelines in this section will help you to optimize system performance when selecting and using database metadata.
Minimizing the Use of Database Metadata Methods
Compared to other JDBC methods, database metadata methods that generate Resultset objects are relatively slow. Applications should cache information returned from result sets that generate database metadata methods so that multiple executions are not needed.
While almost no JDBC application can be written without database metadata methods, you can improve system performance by minimizing their use. To return all result column information mandated by the JDBC specification, a JDBC driver may have to perform complex queries or multiple queries to return the necessary result set for a single call to a database metadata method. These particular elements of the SQL language are performance expensive.
Applications should cache information from database metadata methods. For example, call getTypeInfo once in the application and cache away the elements of the result set that your application depends on. It is unlikely that any application uses all elements of the result set generated by a database metadata method, so the cache of information should not be difficult to maintain.
Avoiding Search Patterns
Using null arguments or search patterns in database metadata methods results in generating time-consuming queries. In addition, network traffic potentially increases due to unwanted results. Always supply as many non-null arguments to result sets that generate database metadata methods as possible.
Because database metadata methods are slow, applications should invoke them as efficiently as possible. Many applications pass the fewest non-null arguments necessary for the function to return success.
For example:
should be:
Sometimes, little information is known about the object for which you are requesting information. Any information that the application can send the driver when calling database metadata methods can result in improved performance and reliability.
Using a Dummy Query to Determine Table Characteristics
Avoid using getColumns to determine characteristics about a table. Instead, use a dummy query with getMetadata.
Consider an application that allows the user to choose columns. Should the application use getColumns to return information about the columns to the user or instead prepare a dummy query and call getMetadata?
Case 1: GetColumns Method
ResultSet WSrc = WSc.getColumns (... "UnknownTable" ...); // This call to getColumns will generate a query to // the system catalogs... possibly a join // which must be prepared, executed, and produce // a result set . . . WSrc.next(); string Cname = getString(4); . . . // user must retrieve N rows from the server // N = # result columns of UnknownTable // result column information has now been obtainedCase 2: GetMetadata Method
// prepare dummy query PreparedStatement WSps = WSc.prepareStatement (... "SELECT * from UnknownTable WHERE 1 = 0" ...); // query is never executed on the server - // only prepared ResultSetMetaData WSsmd=wsps.getMetaData(); int numcols = WSrsmd.getColumnCount(); ... int ctype = WSrsmd.getColumnType(n) ... // result column information has now been obtained // Note we also know the column ordering within the table! // This information cannot be assumed from the getColumns example.In both cases, a query is sent to the server, but in Case 1 the query must be evaluated and form a result set that must be sent to the client. Clearly, Case 2 is the better performing model.
To somewhat complicate this discussion, let us consider a DBMS server that does not natively support preparing a SQL statement. The performance of Case 1 does not change but Case 2 increases minutely because the dummy query must be evaluated instead of only prepared. Because the Where clause of the query always evaluates to FALSE, the query generates no result rows and should execute without accessing table data. For this situation, Case 2 still outperforms Case 1.
Retrieving Data
This section provides general guidelines for retrieving data with JDBC applications.
Retrieving Long Data
Unless it is necessary, applications should not request long data because retrieving long data across a network is slow and resource-intensive.
Most users don't want to see long data. If the user does need to see these result items, the application can query the database again, specifying only the long columns in the select list. This method allows the average user to retrieve result sets without having to pay a high performance penalty for network traffic.
Although the best method is to exclude long data from the select list, some applications do not formulate the select list before sending the query to the JDBC driver (for example, some applications
SELECT * FROMtable name...). If the select list contains long data, the driver must retrieve that data at fetch time, even if the application does not get the long data in the result set. When possible, the application developer should use a method that does not retrieve all columns of the table.Additionally, although the getClob and getBlob methods allow the application to control how long data is retrieved in the application, the designer must realize that in many cases, the JDBC driver emulates these methods due to the lack of true locator support in the DBMS. In such cases, the driver must retrieve all of the long data across the network before exposing the getClob and getBlob methods.
Sometimes long data must be retrieved. When this is the case, remember that most users do not want to see 100 KB, or more, of text on the screen.
Reducing the Size of Data Retrieved
To reduce network traffic and improve performance, you can reduce the size of data being retrieved to a manageable limit by calling setMaxRows, setMaxFieldSize, and the driver-specific SetFetchSize. Another method of reducing the size of the data being retrieved is to decrease the column size. If the driver allows you to define the packet size, use the smallest packet size that will meet your needs.
In addition, be careful to return only the rows you need. If you return five columns when you only need two columns, performance is decreased, especially if the unnecessary rows include long data.
Choosing the Right Data Type
Retrieving and sending certain data types can be expensive. When you design a schema, select the data type that can be processed most efficiently. For example, integer data is processed faster than floating-point data. Floating-point data is defined according to internal database-specific formats, usually in a compressed format. The data must be decompressed and converted into a different format so that it can be processed by the wire protocol.
Processing time is shortest for character strings, followed by integers, which usually require some conversion or byte ordering. Processing floating-point data and timestamps is at least twice as slow as integers.
Selecting JDBC Objects and Methods
The guidelines in this section will help you to optimize system performance when selecting and using JDBC objects and methods.
Using Parameter Markers as Arguments to Stored Procedures
When calling stored procedures, always use parameter markers for the argument markers instead of using literal arguments. JDBC drivers can call stored procedures on the database server either by executing the procedure as any other SQL query, or by optimizing the execution by invoking a Remote Procedure Call (RPC) directly into the database server. Executing the stored procedure as a SQL query results in the database server parsing the statement, validating the argument types, and converting the arguments into the correct data types. Remember that SQL is always sent to the database server as a character string, for example, "
{call getCustName (12345)}". In this case, even though the application programmer might assume that the only argument to getCustName is an integer, the argument is actually passed inside a character string to the server. The database server would parse the SQL query, isolate the single argument value12345, then convert the string`12345'into an integer value.By invoking an RPC inside the database server, the overhead of using a SQL character string is avoided. Instead, the procedure is called only by name with the argument values already encoded into their native data types.
Case 1
Stored Procedure cannot be optimized to use a server-side RPC. The database server must parse the statement, validate the argument types, and convert the arguments into the correct data types.The database server must parse the statement, validate the argument types, and convert the arguments into the correct data types.
CallableStatement cstmt = conn.prepareCall ("call getCustName (12345)"); ResultSet rs = cstmt.executeQuery ();Case 2
Stored Procedure can be optimized to use a server-side RPC. Because the application calls the procedure by name and the argument values are already encoded, the load on the database server is less.
CallableStatement cstmt - conn.prepareCall ("Call getCustName (?)"); cstmt.setLong (1,12345); ResultSet rs = cstmt.executeQuery();Using the Statement Object instead of the PreparedStatement Object
JDBC drivers are optimized based on the perceived use of the functions that are being executed. Choose between the PreparedStatement object and the Statement object depending on the planned use. The Statement object is optimized for a single execution of a SQL statement. In contrast, the PreparedStatement object is optimized for SQL statements that will be executed two or more times.
The overhead for the initial execution of a PreparedStatement object is high. The advantage comes with subsequent executions of the SQL statement.
Choosing the Right Cursor
Choosing the appropriate type of cursor allows maximum application flexibility. This section summarizes the performance issues of three types of cursors.
A forward-only cursor provides excellent performance for sequential reads of all of the rows in a table. However, it cannot be used when the rows to be returned are not sequential.
Insensitive cursors used by JDBC drivers are ideal for applications that require high levels of concurrency on the database server and require the ability to scroll forwards and backwards through result sets. The first request to an insensitive cursor fetches all of the rows and stores them on the client. Thus, the first request is very slow, especially when long data is retrieved. Subsequent requests do not require any network traffic and are processed quickly. Because the first request is processed slowly, insensitive cursors should not be used for a single request of one row. Designers should also avoid using insensitive cursors when long data is returned, because memory can be exhausted. Some insensitive cursor implementations cache the data in a temporary table on the database server and avoid the performance issue.
Sensitive cursors, sometimes called keyset-driven cursors, use identifiers, such as a ROWID, that already exist in your database. When you scroll through the result set, the data for the identifiers is retrieved. Because each request generates network traffic, performance can be very slow. However, returning nonsequential rows does not further affect performance. Sensitive cursors are the preferred scrollable cursor model for dynamic situations, when the application cannot afford to buffer the data from an insensitive cursor.
Using get Methods Effectively
JDBC provides a variety of methods to retrieve data from a result set, such as getInt, getString, and getObject. The getObject method is the most generic and provides the worst performance when the non-default mappings are specified. This is because the JDBC driver must do extra processing to determine the type of the value being retrieved and generate the appropriate mapping. Always use the specific method for the data type.
To further improve performance, provide the column number of the column being retrieved, for example, getString(1), getLong(2), and getInt(3), instead of the column name. If the column names are not specified, network traffic is unaffected, but costly conversions and lookups increase. For example, suppose you use:
The JDBC driver may have to convert
footo uppercase (if necessary), and then comparefoowith all the columns in the column list. That is costly. If, instead, the driver was able to go directly to result column 23, then a lot of processing would be saved.For example, suppose you have a result set that has 15 columns and 100 rows, and the column names are not included in the result set. You are interested in three columns, EMPLOYEENAME (a string), EMPLOYEENUMBER (a long integer), and SALARY (an integer). If you specify getString("EmployeeName"), getLong("EmployeeNumber"), and getInt("Salary"), each column name must be converted to uppercase, and lookups would increase considerably. Performance would improve significantly if you specify getString(1), getLong(2), and getInt(15).
Designing JDBC Applications
The guidelines in this section will help you to optimize system performance when designing JDBC applications.
Managing Connections
Connection management is important to application performance. Optimize your application by connecting once and using multiple statement objects, instead of performing multiple connections. Avoid connecting to a data source after establishing an initial connection.
Although gathering driver information at connect time is a good practice, it is often more efficient to gather it in one step rather than two steps. For example, some applications establish a connection and then call a method in a separate component that reattaches and gathers information about the driver. Applications that are designed as separate entities should pass the established connection object to the data collection routine instead of establishing a second connection.
Another bad practice is to connect and disconnect several times throughout your application to perform SQL statements. Connection objects can have multiple statement objects associated with them. Statement objects, which are defined to be memory storage for information about SQL statements, can manage multiple SQL statements.
You can improve performance significantly with connection pooling, especially for applications that connect over a network or through the World Wide Web. Connection pooling lets you reuse connections. Closing connections does not close the physical connection to the database. When an application requests a connection, an active connection is reused, thus avoiding the network input/output needed to create a new connection.
Connection and statement handling should be addressed before implementation. Spending time and thoughtfully handling connection management improves application performance and maintainability.
Managing Commits in Transactions
Committing transactions is extremely disk I/O intensive and slow. Always turn off autocommit by using the following setting:
WSConnection.setAutoCommit(false).What does a commit actually involve? The database server must flush back to disk every data page that contains updated or new data. This is not a sequential write but a searched write to replace existing data in the table. By default, Autocommit is on when connecting to a data source, and Autocommit mode usually impairs performance because of the significant amount of disk input/output needed to commit every operation.
Furthermore, some database servers do not provide an Autocommit mode. For this type of server, the JDBC driver must explicitly issue a COMMIT statement and a BEGIN TRANSACTION statement for every operation sent to the server. In addition to the large amount of disk input/output required to support Autocommit mode, a performance penalty is paid for up to three network requests for every statement issued by an application.
Although using transactions can help application performance, do not take this tip too far. Leaving transactions active can reduce throughput by holding locks on rows for long times, preventing other users from accessing the rows. Commit transactions in intervals that allow maximum concurrency.
Choosing the Right Transaction Model
Many systems support distributed transactions; that is, transactions that span multiple connections. Distributed transactions are at least four times slower than normal transactions due to the logging and network input/output necessary to communicate between all the components involved in the distributed transaction. Unless distributed transactions are required, avoid using them. Instead, use local transactions whenever possible.
For the best system performance, design the application to run under a single Connection object.
Updating Data
This section provides general guidelines to help you to optimize system performance when updating data in databases.
Using updateXXX Methods
Although programmatic updates do not apply to all types of applications, developers should attempt to use programmatic updates and deletes. Using the updateXXX methods of the ResultSet object allows the developer to update data without building a complex SQL statement. Instead, the developer simply supplies the column in the result set that is to be updated and the data that is to be changed. Then, before moving the cursor from the row in the result set, the updateRow method must be called to update the database as well.
In the following code fragment, the value of the Age column of the Resultset object rs is retrieved using the method getInt, and the method updateInt is used to update the column with an int value of 25. The method updateRow is called to update the row in the database that contains the modified value.
int n = rs.getInt("Age"); // n contains value of Age column in the resultset rs . . . rs.updateInt("Age", 25); rs.updateRow();In addition to making the application more easily maintainable, programmatic updates usually result in improved performance. Because the database server is already positioned on the row for the Select statement in process, performance-expensive operations to locate the row to be changed are not needed. If the row must be located, the server usually has an internal pointer to the row available (for example, ROWID).
Using getBestRowIdentifier()
Use getBestRowIdentifier() to determine the optimal set of columns to use in the Where clause for updating data. Pseudo-columns often provide the fastest access to the data, and these columns can only be determined by using getBestRowIdentifier().
Some applications cannot be designed to take advantage of positional updates and deletes. Some applications might formulate the Where clause by using all searchable result columns by calling getPrimaryKeys(), or by calling getIndexInfo() to find columns that might be part of a unique index. These methods usually work, but might result in fairly complex queries.
Consider the following example:
ResultSet WSrs = WSs.executeQuery ("SELECT first_name, last_name, ssn, address, city, state, zip FROM emp"); // fetchdata ... WSs.executeUpdate ("UPDATE EMP SET ADDRESS = ? WHERE first_name = ? and last_name = ? and ssn = ? and address = ? and city = ? and state = ? and zip = ?"); // fairly complex queryApplications should call getBestRowIdentifier() to retrieve the optimal set of columns (possibly a pseudo-column) that identifies a specific record. Many databases support special columns that are not explicitly defined by the user in the table definition but are hidden columns of every table (for example, ROWID and TID). These pseudo-columns generally provide the fastest access to the data because they typically are pointers to the exact location of the record. Because pseudo-columns are not part of the explicit table definition, they are not returned from getColumns. To determine if pseudo-columns exist, call getBestRowIdentifier().
Consider the previous example again:
... ResultSet WSrowid = getBestRowIdentifier() (.... "emp", ...); // Suppose this returned "ROWID" ... ResultSet WSrs = WSs.executeQuery("SELECT first_name, last_name, ssn, address, city, state, zip, ROWID FROM emp"); // fetch data and probably "hide" ROWID from the user ... WSs.executeUpdate ("UPDATE emp SET address = ? WHERE ROWID = ?"); // fastest access to the data!If your data source does not contain special pseudo-columns, then the result set of getBestRowIdentifier() consists of the columns of the most optimal unique index on the specified table (if a unique index exists). Therefore, your application does not need to call getIndexInfo to find the smallest unique index.